<?php
/**
 * Created by PhpStorm.
 * User: Mars.Li
 * Date: 2015/10/23
 * Time: 10:18
 */
namespace Admin\Model;
class ReportsModel extends CommonModel {

    public  $cols = array('营业收入','销售退货','营业成本','退货成本','物流成本','营业毛利','营业毛利率','费用合计','薪酬福利','营销费用','办公费用','财务费用','生产运营费','其他费用','分摊总部费用','分摊美国费用','营业外收入','投资收益','其他收益','营业外支出','利润合计','利润率');

    public  $callback = array('营业毛利'=>'getGrossProfit','营业毛利率'=>'getGrossProfitMargin','费用合计'=>'getTotalCost','营业外收入'=>'getOtherIncome','利润合计'=>'getProfit','利润率'=>'getProfixMargin');

    public  $rows = array('企业中心','电商中心','总部','万力齐创','创联腾达','润达同创','诚源佳创','Recore美国翻新','USA Office','精锐时代','恒冠同创','爱米可可','合计');

    public $fristNode = array(0,2,5,6,7,16,19,20,21);


    /**
     * 通过日期筛选出利润表报数据
     * @param string $parName  参数 @StarDate 开始时间  @EndDate结束时间
     * @return array 返回二维数组
     */
    public  function getProfitReport($parName = '',$ct){

        if( $parName == ''){
            $parName['@StarDate'] = date('Ym01',strtotime("-1 months",time()));
            $parName['@EndDate']  = date('Ym31',strtotime("-1 months",time()));
        }
        Vendor('U8.Api');
        $u8 = new \U8Api("MSDB_REPORT_CONFIG");
        $u8->p_name='Proc_getU8codeWF';

        $list = $u8->callProcedur($parName);
        $listarr = array();
        foreach ($this->cols as $i=>$col){
            foreach ($this->rows as $j=>$row){
                $listarr[$i][$j]=0;
            }
        }
        foreach ($list as $key => $item) {
            for($i =0;$i<count($this->cols);$i++) {
                if (iconv('GB2312','UTF-8',$item['CitemName']) == $this->cols[$i]) {
                    for($j=0;$j<count($this->rows);$j++) {
                        if (iconv('GB2312','UTF-8',$item['Dw_Name']) == $this->rows[$j]) {
                            $listarr[$i][$j] = $item['FS_je'] * $ct;
                        }
                    }
                }else{

                }
            }
        }
        $input=array();
        foreach ($listarr as $col=>$val){
            foreach ($val as $row=>$v){
                $input[$row][$this->cols[$col]]=$v;
            }
        }

        //利润报表
        $listarr=$this->newlist($listarr, $input);
        return $listarr;
    }


    private function newlist($list,$input)
    {
        foreach ($list as $col=>$val){
            foreach ($val as $row=>$v){
                $call_func = $this->callback[$this->cols[$col]];
                if($call_func){
                    $num = call_user_func_array(array($this,$call_func),array($input[$row]));
                    $list[$col][$row]= $num;
                }
            }
        }
        return $list;
    }

    public function getTotalCost($input)
    {
        return $input['薪酬福利']+$input['营销费用']+$input['办公费用']+$input['财务费用']+$input['生产运营费']+$input['其他费用']+$input['分摊总部费用']+$input['分摊美国费用'];
    }

    public function getGrossProfitMargin($input)
    {
        return $this->getGrossProfit($input)/$input['营业收入'];
    }

    public function getGrossProfit($input)
    {
        return $input['营业收入']-$input['营业成本'];
    }

    public function getOtherIncome($input)
    {
        return $input['投资收益']+$input['其他收益'];
    }

    public function getProfit($input)
    {
        return $input['营业收入']-$input['营业成本']-$this->getTotalCost($input)+$this->getOtherIncome($input)-$input['营业外支出'];

    }

    public function getProfixMargin($input)
    {
        return $this->getProfit($input)/$input['营业收入'];
    }

    public function getU8Const(){
        Vendor('U8.Api');
        return new \U8Api("MSDB_U8COST_CONFIG");
    }
    /**
     * @param $where
     * @return mixed
     */
    public function getQueryConstTable($where) {
        return $this->getU8Const()->_getLink()->table('Tab_ZZ_KMYE')->field('imonth,BQ_DFBB')->where($where)->order('imonth')->limit(1000)->select();
    }

    /**
     * 获取销售费用报表
     * @return array
     */
    public function getConstOfSalesReport($year = '',$ct){
        $year = $year == '' ? date('Y',time()):$year;

        $costu8 = new \U8Api("MSDB_U8COST_CONFIG");
        $result = array();
        $where['U8_ZT'] = '001';
        $where['ccode'] = '6001';
        $where['iyear'] = $year;

        $costs = $this->getQueryConstTable($where);
        $countbq = $costu8->_getLink()->table('Tab_ZZ_KMYE')->field('sum(BQ_DFBB) as sum')->where($where)->find();
        $result['total_cost'] = $countbq['sum']*$ct;
        $new_costs = array();

        foreach ($costs as $key=> $c) {
            $c['BQ_DFBB'] = $c['BQ_DFBB'] * $ct;
            $new_costs [] = $c;
        }
        $result['costs'] = $new_costs;

        $where['ccode'] = '6601';
        $data1 =  $this->getQueryConstTable($where);
        $where['ccode'] = '6602';
        $data2 =  $this->getQueryConstTable($where);
        $sales = array();
        $total_sale = 0;
        foreach ($data1 as $key=> $v) {
            $item = $v['BQ_DFBB'] + $data2[$key]['BQ_DFBB'];
            $sales[] = $item*$ct;
            $total_sale += $item*$ct;
        }
        $result['total_sale'] = $total_sale;
        $result['sales'] = $sales;
        return $result;
    }

    /**
     *  u8中心费用汇总表
     */
    public function getCenterConstReport($parem = '企业中心',$year = '',$ct) {
        $year = $year == '' ? date('Y',time()):$year;
        $profitmodel = D('profit_report');
        $where['Dw_Name'] = $parem;
        $where['year'] = $year;
        $where['CitemName'] = array('in',array('薪酬福利','营销费用','办公费用','财务费用','生产运营费','其他费用','分摊总部费用','分摊美国费用'));
        //查询费用->cache(true,24*3600)
        $data = $profitmodel->where($where)->select();
        $newlist = array();
        for ($i=1;$i<=12;$i++) {
            foreach ($data as $v) {
                if ($v['month'] == $i) {
                    if ($v['fs_je'] > 0) {
                       $newlist['list'][$i-1] += $v['fs_je'] * $ct;
                    }
                }
            }
            $newlist['sum'] += $newlist['list'][$i-1];
        }

        $where['U8_ZT'] = '001';
        if ($parem == '企业中心') {
            $where['ccode'] = '600102';
        } else {
            $where['ccode'] = '600103';
        }
        //销售额
        $where['iyear'] = $year;

        $sales = $this->getQueryConstTable($where);
        $newlist_sales = array();
        foreach($sales as $val) {
            $val['BQ_DFBB'] = $val['BQ_DFBB'] * $ct;
            $newlist_sales [] = $val;
        }
        $newlist['sales'] = $newlist_sales;
        $total_sales =  $this->getU8Const()->_getLink()->table('Tab_ZZ_KMYE')->field('sum(BQ_DFBB) as sum')->where($where)->find();
        $newlist['total_sales'] = $total_sales['sum'] * $ct;

        return $newlist;
    }

    /**
     * 报表权限设置
     * @param $groupId
     * @param $userIdList
     * @return bool
     */
    function setReportUsers($groupId,$userIdList)
    {
        if(empty($userIdList)) {
            return true;
        }
        if(is_string($userIdList)) {
            $userIdList = explode(',',$userIdList);
        }
        array_walk($userIdList, array($this, 'fieldFormat'));
        $userIdList	 =	 implode(',',$userIdList);
        $where = 'a.id ='.$groupId.' AND b.id in('.$userIdList.')';

        $result = $this->execute('INSERT INTO '.$this->tablePrefix.'report_user (report_id,user_id) SELECT a.id, b.id FROM '.$this->tablePrefix.'reports a, '.$this->tablePrefix.'user b WHERE '.$where);
        if($result===false) {
            return false;
        }else {
            return true;
        }
    }


    function getReportUserList($groupId)
    {
        $table = $this->tablePrefix.'report_user';
        $rs = $this->db->query('select b.id,b.nickname,b.email from '.$table.' as a ,'.$this->tablePrefix.'user as b where a.user_id=b.id and  a.report_id='.$groupId.' ');
        return $rs;
    }



    function delReportUser($groupId)
    {
        $table = $this->tablePrefix.'report_user';

        $result = $this->db->execute('delete from '.$table.' where report_id='.$groupId);
        if($result===false) {
            return false;
        }else {
            return true;
        }
    }

    protected function fieldFormat(&$value)
    {
        if(is_int($value)) {
            $value = intval($value);
        } else if(is_float($value)) {
            $value = floatval($value);
        }else if(is_string($value)) {
            $value = '"'.addslashes($value).'"';
        }
        return $value;
    }


    function getRoleReportList() {
        $user_id =  $_SESSION[C('USER_AUTH_KEY')];
        $table = $this->tablePrefix.'report_user';
        $rs = $this->db->query('select b.* from '.$table.' as a ,'.$this->tablePrefix.'reports as b where a.report_id=b.id and  a.user_id='.$user_id.' order by b.report_order');
        return $rs;
    }



    /*********************************************************************************************************************************************************************************************************/
//纵轴表头
public  $lists = array(
    '1' 	=> array('name'=> '营业收入',                                        'value'=>'getOparateCost','last'=>'true'),
    '2'	    => array('name'=> '减：营业成本',                                    'value'=>'getOprateIncome'),
    '3' 	=> array('name'=> '营业税金及附加',                                  'value'=>'6403','tag'=>'BQ_JFBB'),
    '4' 	=> array('name'=> '销售费用',                                        'value'=>'6601','tag'=>'BQ_JFBB'),
    '5' 	=> array('name'=> '管理费用',                                        'value'=>'6602','tag'=>'BQ_JFBB'),
    '6'	    => array('name'=> '财务费用（收益以“—”号填列)',                      'value'=>'6603','tag'=>'BQ_JFBB'),
    '7'	    => array('name'=> '资产减值准备',                                    'value'=>'6701','tag'=>'BQ_JFBB'),
    '8'  	=> array('name'=> '加：公允价值变动净收益（净损失以“—”号填列)',      'value'=>'6101','tag'=>'BQ_JFBB'),
    '9'	    => array('name'=> '投资净收益（净损失以“—”号填列）',                 'value'=>'6111','tag'=>'BQ_JFBB'),
    '10'	=> array('name'=> '其中对联营企业与合营企业的投资收益',              'value'=>''),
    '11'	=> array('name'=> '营业利润',                                        'value'=>'getOprateProfit','last'=>'true'),
    '12'	=> array('name'=> '加：营业外收入',                                  'value'=>'6301','tag'=>'BQ_DFBB'),
    '13'	=> array('name'=> '减：营业外支出',                                  'value'=>'6711','tag'=>'BQ_JFBB'),
    '14'	=> array('name'=> ' 其中：非流动资产处置净损失（净收益以"-"号填列）','value'=>'',),
    '15'	=> array('name'=> '三、利润总和（亏损总额以“—”号填列）',             'value'=>'getTotalProfit','last'=>'true'),
    '16'	=> array('name'=> ' 减：所得税',                                     'value'=>'6801','tag'=>'BQ_JFBB',),
    '17'	=> array('name'=> '净利润（亏损以“—”号填列）',                       'value'=>'getRetainProfit','last'=>'true'),
);
//横轴表头
public $headers = array('项目'=>'param','行次'=>'num','本年累计'=>'yeartotal','本月'=> 'thisMonth','12月'=>'12','11月'=>'11','10月'=>'10','09月'=>'9','08月'=>'8','07月'=>'7','06月'=>'6','05月'=>'5','04月'=>'4','03月'=>'3','02月'=>'2','01月'=>'1');

    //涉及到的CCODE 做配置映射(未全部用配置参数替代CCODE 待完善)
public $businessTax          ='6403';                //营业税金
public $saleCost             ='6601';                //销售费用
public $manageCost           ='6602';                //管理费用
public $financeCost          ='6603';                //财务费用
public $decreaseCost         ='6701';                //资产减值
public $fairValue            ='6101';                //公允价值变动
public $investIncome         ='6111';                //投资价值净收益
public $notBusinessIncome    ='6301';                //营业外收入
public $notBusinessCost      ='6711';                //营业外支出
public $incomeTax            ='6801';                //所得税

    //涉及到的方法 做配置映射(配置参数无法赋值 待完善)
public $businessIncome  ;                  //营业收入
public $businessCost    ;                  //营业成本
public $businessProfit  ;                  //营业利润
public $totalProfit     ;                  //利润总额
public $retainProfit    ;                  //净利润


/**
 * 通过日期筛选出利润表报数据
 * @param string $parName  参数 @StarDate 开始时间  @EndDate结束时间
 * @return array 返回二维数组
 */
public  function getMemberProfitReport($where,$ct) {
    //1.实例化数据库
    Vendor('U8.Api');
    $u8 = new \U8Api("MSDB_U8COST_CONFIG");

    //2.通过条件查出结果
    $ccode = array('6001', '6051', '6401', '6402', '6403', '6601', '6602', '6603', '6701', '6101', '6111', '6301', '6711', '6801');
    $where['ccode'] = array('in', $ccode);
    $lists = $u8->_getLink()->table('Tab_ZZ_KMYE')->where($where)->order('imonth')->limit(10000)->select();

    //3.根据展示页面重构数据
    $html = '<table class="table table-bordered table-striped" id="member_profit_report">';
    $html .= '<thead><tr>';
    //3.1表头
    foreach ($this->headers as $key => $r) {
        $html .= '<th class="text-center"  ;">' . $key . '</th>';
    }
    $html .= '</tr></thead><tbody>';
    //3.2数据主体
    foreach ($lists as $key => $list) {
        $item[$list['imonth']][$list['ccode']]['BQ_JFBB'] = $list['BQ_JFBB'];
        $item[$list['imonth']][$list['ccode']]['BQ_DFBB'] = $list['BQ_DFBB'];
    }
    $i = 1;
    //3.2.1将数据组合为 以横排和竖排2个表头为键的三维数组
    foreach ($this->lists as $list => $attri) {
        foreach ($this->headers as $header => $val) {
            if ($val != 'param' || $val != 'yeartotal') {
                if ($val == 'thisMonth') $val = date('m');
                if ($val == 'num') {
                     $data1[$attri['name']][$header] = $i;//行次
                } elseif ($val != 'num' && !is_numeric($attri['value']) && !empty($attri['value'])) {
                     $data1[$attri['name']][$header] = call_user_func_array(array($this, $attri['value']), array($item[$val]) == 0 ? '' : array($item[$val]));//数据库没有直接对应字段，需通过算式计算的数据
                } elseif ($val != 'num' && is_numeric($attri['value']) && $attri['tag']) {
                     $data1[$attri['name']][$header] = $item[$val][$attri['value']][$attri['tag']];//数据库能直接获取的字段
                } else {
                    $data1[$attri['name']][$header] = '';//表格中存在 但暂时没有用到的部分
                }
            }
        }
        $i++;
    }
    //3.2.2 循环数组累加得出 年总和
    foreach($data1 as $key=>$list){
        foreach($list as $name=>$item){
            if($name!='行次' && $name!='本月') {//以纵轴的表头为键,将1到12月数据累加
                $data2[$key]['本年累计'] += $item;
            }
        }
    }
    //3.2.3将年总和合并至报表数据中，得出与所需表格单元格一一对应的数组
    $data =  array_merge_recursive($data1,$data2);
    //3.2.4将得出数组循环 并添加HTML标签得出输出字符串
    foreach ($this->lists as $list => $attri) {
        //纵轴表头部分
        if ($attri['last']) {
            $html .= '<tr><td style="font-weight:bold;font-size:12px;"><b>' . $attri['name'] . '</b></td>';
        } else {
            $html .= '<tr><td  class="text-center">' . $attri['name'] . '</td>';
        }
        foreach ($this->headers as $header => $val) {
            if($val!='param') {
                $html .= '<td  class="text-center">';
                if ($val == 'yeartotal') {//如果是总和这一列
                    if($data[$attri['name']][$header]['1']){
                        //排除行次这一列 做元/万的切换
                        $html .=  $header=='行次'?round($data[$attri['name']][$header]['1'],1):round($data[$attri['name']][$header]['1']*$ct,1);
                    }
                    else{
                        //如果数据为0 则显示为空
                        $html.='';
                    }
                } else {
                    if($data[$attri['name']][$header]){
                        $html .=  $header=='行次'?round($data[$attri['name']][$header],1):round($data[$attri['name']][$header]*$ct,1);
                    }
                    else{
                        $html.='';
                    }
                }
                $html .= '</td>';
            }
        }
        $html .= '</tr>';
    }
    $html .= '</tbody></table>';

    return $html;

}

//营业收入
    public function getOparateCost($input) {
        return $businessCost = $input['6001']['BQ_JFBB']+$input['6051']['BQ_JFBB'];
    }

//营业成本
    public function getOprateIncome($input) {
        return $businessIncome = $input['6401']['BQ_DFBB']+$input['6402']['BQ_DFBB'];
    }

//营业利润 = 营业收入-(营业成本+营业税金及附加+销售费用+管理费用+财务费用+资产减值损失)+(公允价值变动净收益+投资净收益)
    public function getOprateProfit($input) {

        return $businessProfit = $input['6001']['BQ_JFBB']+$input['6051']['BQ_JFBB']//营业收入
                               - ($input['6401']['BQ_DFBB']+$input['6402']['BQ_DFBB']+$input[$this->saleCost]['BQ_JFBB']+$input[$this->manageCost]['BQ_JFBB']+$input[$this->financeCost]['BQ_JFBB']+$input[$this->businessTax]['BQ_JFBB']+$input[$this->decreaseCost]['BQ_JFBB'])//营业成本+营业税金及附加+销售费用+管理费用+财务费用+资产减值损失
                               + ($input[$this->fairValue]['BQ_JFBB']+$input[$this->investIncome]['BQ_JFBB']);//(公允价值变动净收益+投资净收益)
    }

//利润总额 = 营业利润+营业外收入-营业外支出
    public function getTotalProfit($input)  {
        return $totalProfit = $input['6001']['BQ_JFBB']+$input['6051']['BQ_JFBB']//营业收入
                            - ($input['6401']['BQ_DFBB']+$input['6402']['BQ_DFBB']+$input[$this->saleCost]['BQ_JFBB']+$input[$this->manageCost]['BQ_JFBB']+$input[$this->financeCost]['BQ_JFBB']+$input[$this->businessTax]['BQ_JFBB']+$input[$this->decreaseCost]['BQ_JFBB'])//营业成本+营业税金及附加+销售费用+管理费用+财务费用+资产减值损失
                            + ($input[$this->fairValue]['BQ_JFBB']+$input[$this->investIncome]['BQ_JFBB'])//(公允价值变动净收益+投资净收益)
                            + $input[$this->notBusinessIncome]['BQ_DFBB']//营业外收入
                            - $input[$this->notBusinessCost]['BQ_JFBB'];//营业外支出
    }

//净利润 = 利润总额 - 净利润
    public function getRetainProfit($input)  {
        return $retainProfit = $input['6001']['BQ_JFBB']+$input['6051']['BQ_JFBB']//营业收入
                             - ($input['6401']['BQ_DFBB']+$input['6402']['BQ_DFBB']+$input[$this->saleCost]['BQ_JFBB']+$input[$this->manageCost]['BQ_JFBB']+$input[$this->financeCost]['BQ_JFBB']+$input[$this->businessTax]['BQ_JFBB'] +$input[$this->decreaseCost]['BQ_JFBB'])//营业成本+营业税金及附加+销售费用+管理费用+财务费用+资产减值损失
                             + ($input[$this->fairValue]['BQ_JFBB']+$input[$this->investIncome]['BQ_JFBB'])//(公允价值变动净收益+投资净收益)
                             + $input[$this->notBusinessIncome]['BQ_DFBB']//营业外收入
                             - $input[$this->notBusinessCost]['BQ_JFBB']//营业外支出
                             - $input[$this->incomeTax]['BQ_JFBB'];//净利润
    }

}